package toXLSConverter;

import getData.GetDataFromFieldsOfFramesXML;

import java.awt.Desktop;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class WriteFrameDataToExcel {
  
private WritableCell cell;
private int totalNumberOfFilledRows;
List<String> numberOfFrame;
List<String> numberOfField;
String nameOfFrameToViewRecievedFromMainFormFullPath;
private Label label;

private static void addLabel(WritableSheet sheet, int column, int row, String s) throws RowsExceededException, WriteException
 {
	Label label;
    label = new Label(column, row, s);
    sheet.addCell(label);	
}

public void writeData(List<String> listOfFieldsFromCurrentFrame,
		List<String> listOfAppointmentsFromCurrentFrame,
		List<String> listOfEditableFromCurrentFrame,
		List<String> listOfUniqueFromCurrentFrame,
		List<String> listOfCouldBeNullFrame,
		List<String> listOfHowToFillFromCurrentFrame,
		List<String> listOfFieldTypeFromCurrentFrame,
		List<String> listOfCommentsFromCurrentFrame,
		List<String> listOfAllAddedFramesInCurrentFrame,
		String nameOfFrameToViewRecievedFromMainForm, 
		String[] listOfAllAddedFramesInCurrentFrameToArray) 
				throws IOException, RowsExceededException, WriteException {

// initiating
	int j=0;
	numberOfFrame = new ArrayList<String>();
    numberOfField = new ArrayList<String>();
	String fileNameOfFrameToViewRecievedFromMainForm = nameOfFrameToViewRecievedFromMainForm + ".xls";
	File file = new File(fileNameOfFrameToViewRecievedFromMainForm);
    WorkbookSettings wbSettings = new WorkbookSettings();
    wbSettings.setLocale(new Locale("en", "EN"));
    WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
    workbook.createSheet(nameOfFrameToViewRecievedFromMainForm, 0);
    WritableSheet excelSheet = workbook.getSheet(0);

//set width of rows and other properties
    setColumnsWidth(excelSheet);

    //Background
    WritableFont cellFont = new WritableFont(WritableFont.TIMES,  11, WritableFont.BOLD);
    WritableCellFormat cellFormat = new WritableCellFormat(cellFont);
    cellFormat.setBackground(Colour.GREY_25_PERCENT);
    cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    cellFormat.setWrap(true);
    
    //No background
    WritableFont cellFontNoBackground = new WritableFont(WritableFont.TIMES,  11, WritableFont.BOLD);
    WritableCellFormat cellFormatNoBackground = new WritableCellFormat(cellFontNoBackground);
    cellFormatNoBackground.setBackground(Colour.WHITE);
    //cellFormatNoBackground.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
    cellFormatNoBackground.setWrap(true);
    
    //Just borders
    WritableFont cellFontOnlyBorders = new WritableFont(WritableFont.TIMES,  11, WritableFont.NO_BOLD);
    WritableCellFormat cellFormatOnlyBorders = new WritableCellFormat(cellFontOnlyBorders);
    cellFormatOnlyBorders.setBackground(Colour.WHITE);
    cellFormatOnlyBorders.setBorder(Border.ALL, BorderLineStyle.THIN);
    cellFormatOnlyBorders.setWrap(true);
    
    // label
    addLabel(excelSheet, cellFormatNoBackground, 0, 0, nameOfFrameToViewRecievedFromMainForm);
    addLabel(excelSheet, cellFormatNoBackground, 0, 1, "");
    // included tables (frames) label
    addLabel(excelSheet, cellFormatNoBackground, 0, 2, "Fields, included in current frame");

    
    // Work with frame table headers
    writeHeadersOfFields(excelSheet, cellFormat);
    
    // information about fields
    totalNumberOfFilledRows = fillDataAboutFields(listOfFieldsFromCurrentFrame,
    		listOfAppointmentsFromCurrentFrame, listOfEditableFromCurrentFrame,
			listOfUniqueFromCurrentFrame, listOfCouldBeNullFrame,
			listOfHowToFillFromCurrentFrame, listOfFieldTypeFromCurrentFrame,
			listOfCommentsFromCurrentFrame, j, excelSheet, cellFormatOnlyBorders);
    
    totalNumberOfFilledRows = totalNumberOfFilledRows + listOfFieldsFromCurrentFrame.size();
 
    // included tables (frames) label
    addLabel(excelSheet, cellFormatNoBackground, 0, totalNumberOfFilledRows, "");
    totalNumberOfFilledRows = totalNumberOfFilledRows + 1;
    addLabel(excelSheet, cellFormatNoBackground, 0, totalNumberOfFilledRows, "Tables, included in frame and their displayed fields");
    
    writeHeadersOfFrames(excelSheet, cellFormat);    
    
    fillDataAboutFrames(listOfAllAddedFramesInCurrentFrame,
		nameOfFrameToViewRecievedFromMainForm,
		listOfAllAddedFramesInCurrentFrameToArray, excelSheet, cellFormatOnlyBorders);    
    
    // write data to file    
    workbook.write();
    try {
		workbook.close();
	} catch (WriteException e) {
		e.printStackTrace();
	}
        
    Desktop.getDesktop().open(new File(fileNameOfFrameToViewRecievedFromMainForm));
 
}

private void addLabel(WritableSheet excelSheet,
		WritableCellFormat cellFormatNoBackground, int column, int row,
		String textOfLabel) throws RowsExceededException, WriteException {
	addLabel(excelSheet, column, row, textOfLabel);
    cell = excelSheet.getWritableCell(column, row);
    cell.setCellFormat(cellFormatNoBackground);
    
    	excelSheet.mergeCells(0, row, 8, row);
	}

private void writeHeadersOfFrames(WritableSheet excelSheet,
		WritableCellFormat cellFormat) throws WriteException,
		RowsExceededException {
	//included tables (frames) header
    String HeaderforFrames[] = new String[3];
    HeaderforFrames[0] = "F.N.";
    HeaderforFrames[1] = "Name of the table";
    HeaderforFrames[2] = "Name of columns";
    
    for (int i = 0; i < HeaderforFrames.length; i++) {
        label = new Label(i, totalNumberOfFilledRows + 1, HeaderforFrames[i]);
        excelSheet.addCell(label);
               
        cell = excelSheet.getWritableCell(i, totalNumberOfFilledRows + 1);
        cell.setCellFormat(cellFormat);
    }
}

private void writeHeadersOfFields(WritableSheet excelSheet,
		WritableCellFormat cellFormat) throws WriteException,
		RowsExceededException {
	String HeaderForFields[] = new String[9];
    HeaderForFields[0] = "F.N.";
    HeaderForFields[1] = "Name of filed";
    HeaderForFields[2] = "Appointment";
    HeaderForFields[3] = "Editable";
    HeaderForFields[4] = "Unique";
    HeaderForFields[5] = "Could be null";
    HeaderForFields[6] = "Field type";
    HeaderForFields[7] = "How to fill";
    HeaderForFields[8] = "Comment";

    // Write the Header to the excel file
    for (int i = 0; i < HeaderForFields.length; i++) {
        label = new Label(i, 3, HeaderForFields[i]);
        excelSheet.addCell(label);
               
        cell = excelSheet.getWritableCell(i, 3);
        cell.setCellFormat(cellFormat);
    }
}

private void setColumnsWidth(WritableSheet excelSheet) {
	excelSheet.setColumnView(0, 5);
    excelSheet.setColumnView(1, 30);
    excelSheet.setColumnView(2, 40);
    excelSheet.setColumnView(3, 9);
    excelSheet.setColumnView(4, 9);
    excelSheet.setColumnView(5, 14);
    excelSheet.setColumnView(6, 30);
    excelSheet.setColumnView(7, 15);
    excelSheet.setColumnView(8, 35);
}

private void fillDataAboutFrames(
		List<String> listOfAllAddedFramesInCurrentFrame,
		String nameOfFrameToViewRecievedFromMainForm,
		String[] listOfAllAddedFramesInCurrentFrameToArray,
		WritableSheet excelSheet, CellFormat cellFormatOnlyBorders) throws RowsExceededException, WriteException {
	// included tables data   
	    totalNumberOfFilledRows = totalNumberOfFilledRows + 2;
	    for (int i = 0; i < listOfAllAddedFramesInCurrentFrame.size(); i++) {
	    	numberOfFrame.add("" + (i + 1));
		}
	    
	    ArrayList<String> listOfAllAddedFieldsInFrame = new ArrayList<String>();
	    String currentFrameWhereWeSearchingFields;
	    //nameOfXMLForFrame = getDataFromFieldsOfFramesXML.getXMLForFrames(nameOfFrameToViewRecievedFromMainForm);
	    GetDataFromFieldsOfFramesXML getDataFromFieldsOfFramesXML = new GetDataFromFieldsOfFramesXML();
	    nameOfFrameToViewRecievedFromMainFormFullPath = getDataFromFieldsOfFramesXML.getXMLForFrames(nameOfFrameToViewRecievedFromMainForm);
	    
	    	for (int i = 0; i < listOfAllAddedFramesInCurrentFrame.size(); i++) {
			currentFrameWhereWeSearchingFields = listOfAllAddedFramesInCurrentFrameToArray[i];
	 		String string = getDataFromFieldsOfFramesXML.getListOfAllFieldsForAddedFrames(nameOfFrameToViewRecievedFromMainFormFullPath, currentFrameWhereWeSearchingFields);
	 		//System.out.println("string " + string);
	 		listOfAllAddedFieldsInFrame.add(string);
		 	}
	 		
		fillNextColumnWithFieldData(numberOfFrame, excelSheet,
				0, totalNumberOfFilledRows, cellFormatOnlyBorders);
	    fillNextColumnWithFieldData(listOfAllAddedFramesInCurrentFrame, excelSheet,
				1, totalNumberOfFilledRows, cellFormatOnlyBorders);
	    fillNextColumnWithFieldData(listOfAllAddedFieldsInFrame, excelSheet,
				2, totalNumberOfFilledRows, cellFormatOnlyBorders);
}

private int fillDataAboutFields(List<String> listOfFieldsFromCurrentFrame,
		List<String> listOfAppointmentsFromCurrentFrame,
		List<String> listOfEditableFromCurrentFrame,
		List<String> listOfUniqueFromCurrentFrame,
		List<String> listOfCouldBeNullFrame,
		List<String> listOfHowToFillFromCurrentFrame,
		List<String> listOfFieldTypeFromCurrentFrame,
		List<String> listOfCommentsFromCurrentFrame, int j,
		WritableSheet excelSheet, CellFormat cellFormatOnlyBorders) throws RowsExceededException, WriteException {
	for (int i = 0; i < listOfFieldsFromCurrentFrame.size(); i++) {
    	numberOfField.add("" + (i+1));
	}
    totalNumberOfFilledRows = 4;
    j = fillNextColumnWithFieldData(numberOfField, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfFieldsFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfAppointmentsFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfEditableFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfUniqueFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfCouldBeNullFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfHowToFillFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfFieldTypeFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    j = fillNextColumnWithFieldData(listOfCommentsFromCurrentFrame, excelSheet,
			j, totalNumberOfFilledRows, cellFormatOnlyBorders);
    return totalNumberOfFilledRows;
}

private int fillNextColumnWithFieldData(
		List<String> listOfFieldsProperties, WritableSheet excelSheet,
		int j, int totalNumberOfFilledRows, CellFormat cellFormatOnlyBorders) throws RowsExceededException,
		WriteException {
	for (int i = 0; i < listOfFieldsProperties.size(); i++) {
		String[] inputColumnArray = listOfFieldsProperties.toArray(new String[0]);
		String string = inputColumnArray[i];
		addLabel(excelSheet, j, i+totalNumberOfFilledRows, string.toString());
		cell = excelSheet.getWritableCell(j, i+totalNumberOfFilledRows);
        cell.setCellFormat(cellFormatOnlyBorders);		
	}
	j = j+1;
	return j;
}

}



